<?php
$app_id="1";
$referral_code="123";
$mysqli = new mysqli("localhost", "david", "Cf222222", "david");

			$statements_json = file_get_contents(app_path()."\\config\\dataStruct\\newjson.json");
                        $date = date("Y-m-d"); 
			$time = date("H:i:s");
			$statement =  json_decode($statements_json,true);
			
			$table2Count = count($statement['accounts']);
			foreach($statement['accounts'] as $account){
				//dump( $account);
				$account_holder = "";//$account['accountHolder'];
				$account_name = mysqli_real_escape_string($mysqli,$account['name']);
				$institution = 'Bank-statement';
				$account_number = $account['accountNumber'];
				$bsb = $account['bsb'];
				$balance = $account['balance'];
				$available = $account['available'];
				$period = $account['statementData']['startDate'].' to '.$account['statementData']['endDate'];
				$opening_balance = $account['statementData']['openingBalance'];
				$total_credits = $account['statementData']['totalCredits'];
				$total_debits = $account['statementData']['totalDebits'];
				$closing_balance = $account['statementData']['closingBalance'];
				$min_balance = $account['statementData']['minBalance'];
				$max_balance = $account['statementData']['maxBalance'];
				$minday_endbalance = $account['statementData']['minDayEndBalance'];
				$error_message = $account['statementData']['errorMessage'];
				$average_balance = $account['statementData']['averageBalance'];
				$daysin_negative = $account['statementData']['daysInNegative'];
				
									
			$statement ="INSERT INTO `bs_bank_statements` (`app_id`, `account_holder`, `account_name`, `institution`, `account_number`, `bsb`,`balance`,`available`, `period`, `referral_code`, `opening_balance`, `total_credits`, `total_debits`, `closing_balance`, `min_balance`,`max_balance`,`minday_endbalance`,`error_message`,`average_balance`,`daysin_negative`) VALUES ('$app_id','$account_holder','$account_name','$institution','$account_number','$bsb','$balance','$available','$period','$referral_code','$opening_balance','$total_credits','$total_debits','$closing_balance','$min_balance','$max_balance','$minday_endbalance','$error_message','$average_balance','$daysin_negative')";
			
			$table2[] = $mysqli->query($statement);
		
			
			$statement_id = mysqli_insert_id($mysqli);
			$statemnet_array[] =$statement_id;
			
			
			//statemnet details
//				    $table3Count[] = count($account['statementData']['details']);
//					foreach($account['statementData']['details'] as $details){
//						$date = $details['date'];
//						$text = mysqli_real_escape_string($mysqli,$details['text']);
//						$notes = $details['notes'];
//						$amount = $details['amount'];
//						$detail_balance = $details['balance'];
//						$type = $details['type'];
//						$tags =mysqli_real_escape_string($mysqli,implode("','", $details['tags']));
//						
//					$statement_details ="INSERT INTO bs_statement_details (`statement_id`, `date`, `text`, `notes`, `amount`,`type` ,`detail_balance`, `tags`) VALUES ('$statement_id','$date','$text','$notes','$amount','$type','$detail_balance','$tags')";
//				$table3[] = $mysqli->query($statement_details) ;
//			echo $statement_details."<br>"
//				}
				
				
				
				
			// analysis		
			
			foreach($account['statementData']['analysis'] as $analysis => $value){
				
				
			//if($analysis == 'Income'){
				
				$type = '';
				$Transaction_Count ='';
				$TotalValue= '';
				$MonthAvg ='';
				$MinValue = '';
				$MaxValue = '';
				$FirstTransaction = '';
				$LastTransaction = '';
				$period = '';
				$periodIsRegular = '';
				foreach ($value as $item => $val){
			       
					$type =  strtolower($analysis.'_'.str_replace(' ', '_', $item));
					$Transaction_Count = $val['transactionCount'];
					$TotalValue = $val['totalValue'];
					$MonthAvg = $val['monthAvg'];
					if($item != 'total'){
                                            $MinValue = $val['minValue'];
                                            $MaxValue = $val['maxValue'];
                                            $FirstTransaction = $val['firstTransaction'];
                                            $LastTransaction = $val['lastTransaction'];
                                            $period = $val['period'];
                                            $periodIsRegular = $val['periodIsRegular'];
					}
				
				$analysis_sql = "INSERT INTO bs_bank_analysis (`statement_id`, `Type`, `Transaction_Count`, `TotalValue`, `MonthAvg`, `MinValue`, `MaxValue`, `FirstTransaction`,`LastTransaction`,`period`,`periodIsRegular`) VALUES ('$statement_id','$type','$Transaction_Count','$TotalValue','$MonthAvg','$MinValue','$MaxValue','$FirstTransaction','$LastTransaction','$period','$periodIsRegular')";
				$table4[] = $mysqli->query($analysis_sql);
                                
				$analysis_id = mysqli_insert_id($mysqli);
				
				if($Transaction_Count >0 && !empty($val['transactions']) ){
					
//					foreach($val['transactions'] as $transaction ){
//						
//						$date = $transaction['date'];
//						$text = mysqli_real_escape_string($mysqli,$transaction['text']);
//						$notes = $transaction['notes'];
//						$amount = $transaction['amount'];
//						$type = $transaction['type'];
//						$trans_balance = $transaction['balance'];
//						$tags =mysqli_real_escape_string($mysqli,implode("','", $transaction['tags']));
//						
//						
//						$transaction_sql = "INSERT INTO bs_analysis_details(`analysis_id`,`date`,`text`,`notes`,`amount`,`type`,`trans_balance`,`tags`) VALUES ('$analysis_id','$date','$text','$notes','$amount','$type','$trans_balance','$tags')";
//						
//						$table5[] = $mysqli->query($transaction_sql);
//						$table5Count[]=1;
//						
//					}
				}
				
				$table4Count[]=1;
					
					
					
				}
	
				
		
			}

			}
			
				        
				
?>